package jm.easyconfig;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.function.Function;

import javax.sql.DataSource;

public class SqlHelper {

	javax.sql.DataSource dataSource;

	public SqlHelper(DataSource dataSource) {
		super();
		this.dataSource = dataSource;
	}

	void queryResultSet(Consumer<ResultSet> consumer, String sql, Object... objects) {
		try (Connection con = dataSource.getConnection(); PreparedStatement st = con.prepareStatement(sql)) {

			for (int i = 0; i < objects.length; i++) {
				st.setObject(i + 1, objects[i]);
			}

			try (ResultSet rs = st.executeQuery()) {
				while (rs.next()) {
					consumer.accept(rs);
				}
			}

		} catch (Throwable e) {
			throw new RuntimeException(e);
		}
	}

	void queryMap(Consumer<Map<String, Object>> consumer, String sql, Object... objects) {

		try (Connection con = dataSource.getConnection()) {

			queryMap(con, consumer, sql, objects);

		} catch (Throwable e) {
			throw new RuntimeException(e);
		}
	}

	void queryMap(Connection con, Consumer<Map<String, Object>> consumer, String sql, Object... objects) {
		try (PreparedStatement st = con.prepareStatement(sql)) {

			for (int i = 0; i < objects.length; i++) {
				st.setObject(i + 1, objects[i]);
			}

			try (ResultSet rs = st.executeQuery()) {
				while (rs.next()) {
					ResultSetMetaData rsmd = rs.getMetaData();
					int width = rsmd.getColumnCount();
					Map<String, Object> row = new HashMap<String, Object>();
					for (int i = 1; i <= width; i++) {
						row.put(rsmd.getColumnName(i), rs.getObject(i));
					}
					consumer.accept(row);
				}
			}

		} catch (Throwable e) {
			throw new RuntimeException(e);
		}
	}

	List<Map<String, Object>> queryMapList(String sql, Object... objects) {
		LinkedList<Map<String, Object>> lst = new LinkedList<Map<String, Object>>();
		queryMap(map -> {
			lst.addLast(map);
		}, sql, objects);
		return lst;
	}

	Map<String, Map<String, Object>> querykey2Map(String keyName, String sql, Object... objects) {
		Map<String, Map<String, Object>> lst = new HashMap<String, Map<String, Object>>();
		queryMap(map -> {
			lst.put(map.get(keyName).toString(), map);
		}, sql, objects);
		return lst;
	}

	Object getScalar(String sql, Object... objects) {
		try (Connection con = dataSource.getConnection()) {
			return getScalar(con, sql, objects);

		} catch (Throwable e) {
			throw new RuntimeException(e);
		}
	}

	Object getScalar(Connection conn, String sql, Object... objects) {
		try (PreparedStatement st = conn.prepareStatement(sql)) {

			for (int i = 0; i < objects.length; i++) {
				st.setObject(i + 1, objects[i]);
			}

			try (ResultSet rs = st.executeQuery()) {
				while (rs.next()) {
					return rs.getObject(1);
				}
				return null;
			}

		} catch (Throwable e) {
			throw new RuntimeException(e);
		}
	}

	int execute(String sql, Object... objects) {
		int ret = prepareExecution((PreparedStatement st) -> {
			int count = 0;
			try {
				for (int i = 0; i < objects.length; i++) {
					st.setObject(i + 1, objects[i]);
				}

				st.execute();

				count = st.getUpdateCount();

			} catch (SQLException e) {
				throw new RuntimeException(e);
			}

			return count;

		}, sql);

		return ret;
	}

	int execute(Connection con, String sql, Object... objects) {
		try (PreparedStatement st = con.prepareStatement(sql)) {
			for (int i = 0; i < objects.length; i++) {
				st.setObject(i + 1, objects[i]);
			}

			st.execute();

			return st.getUpdateCount();

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	int prepareExecution(Function<PreparedStatement, Integer> function, String sql) {
		Integer ret = 0;
		try (Connection con = dataSource.getConnection()) {

			con.setAutoCommit(false);

			try (PreparedStatement st = con.prepareStatement(sql)) {

				ret = function.apply(st);

				con.commit();

			} catch (Exception e) {
				con.rollback();
				throw new RuntimeException(e);
			}

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return ret;
	}

	<T> T processInTransaction(Function<Connection, T> consumer) {
		T ret = null;
		try (Connection con = dataSource.getConnection()) {
			con.setAutoCommit(false);

			try {

				ret = consumer.apply(con);

				con.commit();

			} catch (Throwable e) {
				con.rollback();
				throw e;
			}

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return ret;

	}

}
